"""
停车场数据分析模块，提供各种数据分析功能
"""

import os
import logging
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import seaborn as sns
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from database import execute_query
from config import DATA_EXPORT_PATH

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger('data_analysis')

# 确保导出目录存在
os.makedirs(DATA_EXPORT_PATH, exist_ok=True)

def get_parking_stats(start_date=None, end_date=None, parking_lot_id=None):
    """
    获取停车场统计数据
    
    Args:
        start_date (str): 起始日期 (YYYY-MM-DD)
        end_date (str): 结束日期 (YYYY-MM-DD)
        parking_lot_id (int): 停车场ID
        
    Returns:
        pandas.DataFrame: 停车场统计数据
    """
    where_clauses = []
    params = []
    
    if start_date:
        where_clauses.append("EntryTime >= ?")
        params.append(start_date)
    
    if end_date:
        where_clauses.append("EntryTime <= ?")
        params.append(end_date + " 23:59:59")
    
    if parking_lot_id:
        where_clauses.append("VE.ParkingLotID = ?")
        params.append(parking_lot_id)
    
    where_sql = " AND ".join(where_clauses)
    if where_sql:
        where_sql = "WHERE " + where_sql
    
    query = f"""
    SELECT 
        PL.ParkingLotName,
        PL.ParkingLotType,
        COUNT(*) AS TotalEntries,
        SUM(CASE WHEN ExitTime IS NOT NULL THEN 1 ELSE 0 END) AS TotalExits,
        SUM(CASE WHEN ExitTime IS NULL THEN 1 ELSE 0 END) AS CurrentlyParked,
        AVG(CASE WHEN ParkingDuration IS NOT NULL THEN ParkingDuration ELSE NULL END) AS AvgParkingDurationMinutes,
        SUM(PaymentAmount) AS TotalRevenue,
        COUNT(DISTINCT VehicleNumber) AS UniqueVehicles
    FROM 
        VehicleEntryExit VE
    JOIN 
        ParkingLot PL ON VE.ParkingLotID = PL.ParkingLotID
    {where_sql}
    GROUP BY 
        PL.ParkingLotName, PL.ParkingLotType
    """
    
    logger.info(f"获取停车场统计数据: {start_date} 至 {end_date}, 停车场ID: {parking_lot_id}")
    return execute_query(query, tuple(params) if params else None)

def get_hourly_traffic(date=None, parking_lot_id=None):
    """
    获取每小时车流量统计
    
    Args:
        date (str): 日期 (YYYY-MM-DD)
        parking_lot_id (int): 停车场ID
        
    Returns:
        pandas.DataFrame: 每小时车流量数据
    """
    where_clauses = []
    params = []
    
    if not date:
        date = datetime.now().strftime('%Y-%m-%d')
    
    where_clauses.append("CONVERT(date, EntryTime) = ?")
    params.append(date)
    
    if parking_lot_id:
        where_clauses.append("ParkingLotID = ?")
        params.append(parking_lot_id)
    
    where_sql = " AND ".join(where_clauses)
    where_sql = "WHERE " + where_sql
    
    query = f"""
    SELECT 
        DATEPART(HOUR, EntryTime) AS HourOfDay,
        COUNT(*) AS EntryCount,
        ParkingLotID
    FROM 
        VehicleEntryExit
    {where_sql}
    GROUP BY 
        DATEPART(HOUR, EntryTime), ParkingLotID
    ORDER BY 
        HourOfDay
    """
    
    logger.info(f"获取每小时车流量: 日期 {date}, 停车场ID: {parking_lot_id}")
    return execute_query(query, tuple(params))

def analyze_peak_hours(days=7, parking_lot_id=None):
    """
    分析高峰时段
    
    Args:
        days (int): 分析的天数
        parking_lot_id (int): 停车场ID
        
    Returns:
        pandas.DataFrame: 高峰时段分析结果
    """
    end_date = datetime.now()
    start_date = end_date - timedelta(days=days)
    
    where_clauses = ["EntryTime BETWEEN ? AND ?"]
    params = [start_date, end_date]
    
    if parking_lot_id:
        where_clauses.append("ParkingLotID = ?")
        params.append(parking_lot_id)
    
    where_sql = " AND ".join(where_clauses)
    where_sql = "WHERE " + where_sql
    
    query = f"""
    SELECT 
        ParkingLotID,
        DATEPART(WEEKDAY, EntryTime) AS DayOfWeek,
        DATEPART(HOUR, EntryTime) AS HourOfDay,
        COUNT(*) AS EntryCount
    FROM 
        VehicleEntryExit
    {where_sql}
    GROUP BY 
        ParkingLotID, DATEPART(WEEKDAY, EntryTime), DATEPART(HOUR, EntryTime)
    ORDER BY 
        ParkingLotID, DayOfWeek, HourOfDay
    """
    
    logger.info(f"分析高峰时段: 过去 {days} 天, 停车场ID: {parking_lot_id}")
    peak_hours_df = execute_query(query, tuple(params))
    
    # 获取停车场名称
    if len(peak_hours_df) > 0:
        lot_ids = peak_hours_df['ParkingLotID'].unique()
        lot_query = "SELECT ParkingLotID, ParkingLotName FROM ParkingLot WHERE ParkingLotID IN ({})".format(
            ','.join(['?'] * len(lot_ids))
        )
        lot_df = execute_query(lot_query, tuple(lot_ids))
        
        # 合并停车场名称
        peak_hours_df = pd.merge(peak_hours_df, lot_df, on='ParkingLotID')
    
    # 添加星期名称
    day_names = {1: '星期一', 2: '星期二', 3: '星期三', 4: '星期四', 5: '星期五', 6: '星期六', 7: '星期日'}
    peak_hours_df['DayName'] = peak_hours_df['DayOfWeek'].map(day_names)
    
    # 找出每个停车场每天的高峰时段（前3小时）
    peak_hours = []
    for lot_id in peak_hours_df['ParkingLotID'].unique():
        for day in range(1, 8):
            day_data = peak_hours_df[(peak_hours_df['ParkingLotID'] == lot_id) & (peak_hours_df['DayOfWeek'] == day)]
            if len(day_data) > 0:
                top_hours = day_data.nlargest(3, 'EntryCount')
                peak_hours.append(top_hours)
    
    if peak_hours:
        result = pd.concat(peak_hours)
        
        # 导出CSV
        output_file = os.path.join(DATA_EXPORT_PATH, f'peak_hours_analysis_{datetime.now().strftime("%Y%m%d")}.csv')
        result.to_csv(output_file, index=False, encoding='utf-8-sig')
        logger.info(f"高峰时段分析结果已导出到: {output_file}")
        
        return result
    else:
        logger.warning("没有足够的数据进行高峰时段分析")
        return pd.DataFrame()

def analyze_parking_duration(days=30, parking_lot_id=None):
    """
    分析停车时长分布
    
    Args:
        days (int): 分析的天数
        parking_lot_id (int): 停车场ID
        
    Returns:
        pandas.DataFrame: 停车时长分析结果
    """
    end_date = datetime.now()
    start_date = end_date - timedelta(days=days)
    
    where_clauses = ["EntryTime BETWEEN ? AND ? AND ExitTime IS NOT NULL"]
    params = [start_date, end_date]
    
    if parking_lot_id:
        where_clauses.append("VE.ParkingLotID = ?")
        params.append(parking_lot_id)
    
    where_sql = " AND ".join(where_clauses)
    where_sql = "WHERE " + where_sql
    
    query = f"""
    SELECT 
        VE.ParkingLotID,
        PL.ParkingLotName,
        VE.ParkingDuration,
        VE.PaymentAmount,
        CASE
            WHEN VE.ParkingDuration <= 60 THEN '1小时以内'
            WHEN VE.ParkingDuration <= 120 THEN '1-2小时'
            WHEN VE.ParkingDuration <= 180 THEN '2-3小时'
            WHEN VE.ParkingDuration <= 240 THEN '3-4小时'
            WHEN VE.ParkingDuration <= 360 THEN '4-6小时'
            WHEN VE.ParkingDuration <= 720 THEN '6-12小时'
            ELSE '12小时以上'
        END AS DurationCategory
    FROM 
        VehicleEntryExit VE
    JOIN
        ParkingLot PL ON VE.ParkingLotID = PL.ParkingLotID
    {where_sql}
    """
    
    logger.info(f"分析停车时长: 过去 {days} 天, 停车场ID: {parking_lot_id}")
    duration_df = execute_query(query, tuple(params))
    
    if len(duration_df) > 0:
        # 按停车时长分类汇总
        summary = duration_df.groupby(['ParkingLotID', 'ParkingLotName', 'DurationCategory']).agg(
            VehicleCount=('ParkingDuration', 'count'),
            AverageDuration=('ParkingDuration', 'mean'),
            AveragePayment=('PaymentAmount', 'mean')
        ).reset_index()
        
        # 计算占比
        total_by_lot = summary.groupby('ParkingLotID')['VehicleCount'].sum().reset_index()
        total_by_lot.rename(columns={'VehicleCount': 'TotalVehicles'}, inplace=True)
        summary = pd.merge(summary, total_by_lot, on='ParkingLotID')
        summary['Percentage'] = summary['VehicleCount'] / summary['TotalVehicles'] * 100
        
        # 导出结果
        output_file = os.path.join(DATA_EXPORT_PATH, f'parking_duration_analysis_{datetime.now().strftime("%Y%m%d")}.csv')
        summary.to_csv(output_file, index=False, encoding='utf-8-sig')
        logger.info(f"停车时长分析结果已导出到: {output_file}")
        
        return summary
    else:
        logger.warning("没有足够的数据进行停车时长分析")
        return pd.DataFrame()

def analyze_device_errors(days=90):
    """
    分析设备故障情况
    
    Args:
        days (int): 分析的天数
        
    Returns:
        pandas.DataFrame: 设备故障分析结果
    """
    end_date = datetime.now()
    start_date = end_date - timedelta(days=days)
    
    query = """
    SELECT 
        GD.ParkingLotID,
        PL.ParkingLotName,
        GD.GateID,
        GD.GateLocation,
        GD.GateType,
        COUNT(DE.ErrorID) AS ErrorCount,
        AVG(CASE WHEN DE.ResolutionTime IS NOT NULL 
                THEN DATEDIFF(MINUTE, DE.ErrorTime, DE.ResolutionTime) 
                ELSE NULL END) AS AvgResolutionTimeMinutes,
        COUNT(CASE WHEN DE.ResolutionTime IS NULL THEN 1 ELSE NULL END) AS UnresolvedErrors
    FROM 
        GateDevice GD
    LEFT JOIN
        DeviceErrors DE ON GD.GateID = DE.GateID AND DE.ErrorTime BETWEEN ? AND ?
    JOIN
        ParkingLot PL ON GD.ParkingLotID = PL.ParkingLotID
    GROUP BY
        GD.ParkingLotID, PL.ParkingLotName, GD.GateID, GD.GateLocation, GD.GateType
    ORDER BY
        ErrorCount DESC
    """
    
    logger.info(f"分析设备故障: 过去 {days} 天")
    device_errors_df = execute_query(query, (start_date, end_date))
    
    if len(device_errors_df) > 0:
        # 导出结果
        output_file = os.path.join(DATA_EXPORT_PATH, f'device_errors_analysis_{datetime.now().strftime("%Y%m%d")}.csv')
        device_errors_df.to_csv(output_file, index=False, encoding='utf-8-sig')
        logger.info(f"设备故障分析结果已导出到: {output_file}")
        
        return device_errors_df
    else:
        logger.warning("没有足够的数据进行设备故障分析")
        return pd.DataFrame()

def predict_traffic(days_to_predict=7, historical_days=30, parking_lot_id=None):
    """
    预测未来车流量
    
    Args:
        days_to_predict (int): 预测的天数
        historical_days (int): 用于预测的历史数据天数
        parking_lot_id (int): 停车场ID
        
    Returns:
        pandas.DataFrame: 车流量预测结果
    """
    end_date = datetime.now()
    start_date = end_date - timedelta(days=historical_days)
    
    where_clauses = ["EntryTime BETWEEN ? AND ?"]
    params = [start_date, end_date]
    
    if parking_lot_id:
        where_clauses.append("VE.ParkingLotID = ?")
        params.append(parking_lot_id)
    
    where_sql = " AND ".join(where_clauses)
    where_sql = "WHERE " + where_sql
    
    query = f"""
    SELECT 
        VE.ParkingLotID,
        PL.ParkingLotName,
        CONVERT(date, VE.EntryTime) AS Date,
        DATEPART(WEEKDAY, VE.EntryTime) AS DayOfWeek,
        COUNT(*) AS EntryCount
    FROM 
        VehicleEntryExit VE
    JOIN
        ParkingLot PL ON VE.ParkingLotID = PL.ParkingLotID
    {where_sql}
    GROUP BY
        VE.ParkingLotID, PL.ParkingLotName, CONVERT(date, VE.EntryTime), DATEPART(WEEKDAY, VE.EntryTime)
    ORDER BY
        VE.ParkingLotID, Date
    """
    
    logger.info(f"预测车流量: 基于过去 {historical_days} 天的数据, 预测未来 {days_to_predict} 天")
    historical_data = execute_query(query, tuple(params))
    
    if len(historical_data) > 0:
        # 将日期转换为日期类型
        historical_data['Date'] = pd.to_datetime(historical_data['Date'])
        
        # 创建预测结果DataFrame
        predictions = []
        
        # 为每个停车场进行预测
        for lot_id in historical_data['ParkingLotID'].unique():
            lot_data = historical_data[historical_data['ParkingLotID'] == lot_id]
            lot_name = lot_data['ParkingLotName'].iloc[0]
            
            # 计算每个星期几的平均车流量
            weekday_avg = lot_data.groupby('DayOfWeek')['EntryCount'].mean().reset_index()
            
            # 预测未来几天
            for i in range(1, days_to_predict + 1):
                predict_date = end_date + timedelta(days=i)
                weekday = predict_date.weekday() + 1  # 转换为SQL Server的星期几表示
                
                # 获取该星期几的平均车流量
                avg_count = weekday_avg[weekday_avg['DayOfWeek'] == weekday]['EntryCount']
                if len(avg_count) > 0:
                    predicted_count = avg_count.iloc[0]
                else:
                    # 如果没有该星期几的历史数据，使用整体平均值
                    predicted_count = lot_data['EntryCount'].mean()
                
                predictions.append({
                    'ParkingLotID': lot_id,
                    'ParkingLotName': lot_name,
                    'Date': predict_date,
                    'DayOfWeek': weekday,
                    'PredictedEntryCount': round(predicted_count),
                    'DayName': ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日'][weekday - 1]
                })
        
        if predictions:
            result = pd.DataFrame(predictions)
            
            # 导出预测结果
            output_file = os.path.join(DATA_EXPORT_PATH, f'traffic_prediction_{datetime.now().strftime("%Y%m%d")}.csv')
            result.to_csv(output_file, index=False, encoding='utf-8-sig')
            logger.info(f"车流量预测结果已导出到: {output_file}")
            
            return result
        else:
            logger.warning("无法生成预测结果")
            return pd.DataFrame()
    else:
        logger.warning("没有足够的历史数据进行车流量预测")
        return pd.DataFrame()

if __name__ == "__main__":
    # 测试数据分析功能
    print("正在测试数据分析功能...")
    
    # 获取停车场统计数据
    stats = get_parking_stats()
    print(f"停车场统计数据: {len(stats)} 条记录")
    
    # 分析高峰时段
    peak_hours = analyze_peak_hours()
    print(f"高峰时段分析: {len(peak_hours)} 条记录")
    
    # 分析停车时长
    duration = analyze_parking_duration()
    print(f"停车时长分析: {len(duration)} 条记录")
    
    # 设备故障分析
    errors = analyze_device_errors()
    print(f"设备故障分析: {len(errors)} 条记录")
    
    # 预测车流量
    prediction = predict_traffic()
    print(f"车流量预测: {len(prediction)} 条记录") 